﻿using System;
using System.Drawing.Drawing2D;
using System.Drawing;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using generic_class;
using my_database;
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop;

namespace pallete_main
{
    class module_excel_gllxt
    {
        MyDatabase m_connection = null;

        //纤芯颜色数组
        string[] m_listColor = { "蓝", "橙", "绿", "棕 ", "灰", "本" };

        //光配
        string[] m_listRow = { "", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };

        public void DrawTable(string strName, int[] device, ProgressBar prg)
        {


                //屏柜列表
                ArrayList lstTang = null;
                ArrayList lstTemp = null;
              /* int[] device = new int[1];
               device[0] = 751;// 749;
               /* device[1] = 751;
                device[2] = 734;
                device[3] = 933;
                * */

                Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
                app.SheetsInNewWorkbook = 1;
                app.DisplayAlerts = false;
                Workbook book = app.Workbooks.Add();

                lstTemp = m_connection.get_tang_from_device(device);

                //没有找到指定的设备，直接返回
                if ((lstTemp.Count == 0))
                {
                    System.Windows.Forms.MessageBox.Show("没有找到设备所属屏柜!");
                }
         
                //处理掉重复的屏柜
                lstTang = new ArrayList();
                for(int i=0;i< lstTemp.Count;i++)
                {
                    bool bFind = false;
                    tang_item item = (tang_item)lstTemp[i];
                    for (int j = 0; j < lstTang.Count; j++)
                    {
                        tang_item item_new = (tang_item)lstTang[j];
                        if (item_new.idtang == item.idtang)
                        {
                            bFind = true;
                            break;
                        }
                    }

                    if (false == bFind)
                        lstTang.Add(item);
                }

                prg.Maximum = lstTang.Count;
                prg.Value = 0;

                //释放内存
                //处理每一个屏柜
                for (int index = 0; index < lstTang.Count; index++)
                {
                    prg.Value++;
                    tang_item header = (tang_item)lstTang[index];
                    Worksheet sheet = book.Sheets.Add() as Worksheet;
                    DrawTangTable(sheet, header);
                }

                book.SaveAs("c:\\1.xlsx");
                book.Close();
                app.Quit();
                app = null;


        }

            
        /*画柜内配置表格*/
        private void DrawTangTable(Worksheet tbl,tang_item header)
        {
            //屏柜中光缆列表
            ArrayList lstFiber = null;

            int row_count = 3;
            int[] device= new int[1];
            device[0] = header.idtang;


            //获取屏柜fiber list
            lstFiber = m_connection.get_cable_from_tang(device);
            //没有fiber
            if (lstFiber.Count == 0)
            {
                System.Windows.Forms.MessageBox.Show("没有找到屏柜光缆信息!");
                return;
            }

            if (header.objectDesc == null)
            {
                tbl.Name = "";
                DrawTableHeader(tbl, "");
            }
            else
            {
                tbl.Name = header.objectDesc;
                DrawTableHeader(tbl, header.objectDesc);
            }


            //循环处理每一个fiber
            for (int i = 0; i < lstFiber.Count; i++)
            {
                int line_count = 0;
                int nIndex = 0;
                cable_item itCalbe = (cable_item)lstFiber[i];
                int[] device1 = new int[1];
                device1[0] = itCalbe.idcable;
                ArrayList lstLine = m_connection.get_line_from_cable(device1);

                //合并套管颜色的部分
                if (lstLine.Count > 0)
                {
                    //增加新的线缆行数
                    int temp_row_count = row_count;

                    //处理每个fiber中每条line
                    for (line_count = 0; line_count < lstLine.Count; line_count++)
                    {
                        ArrayList lstLineInfor = null;
                        //纤芯编号从1开始所以line_count + 1;
                        line_item itLine = (line_item)lstLine[line_count];
                        lstLineInfor = m_connection.get_line_info(itLine.idline , header.idtang);
                        for (int j = 0; j < lstLineInfor.Count; j++)
                        {
                            line_info line_infor = (line_info)lstLineInfor[j];
                            row_count++;
                            DrawRows(tbl, line_infor, row_count, line_count + 1);
                        }

                    }

                    Range range = tbl.get_Range(tbl.Cells[temp_row_count+1, 1], tbl.Cells[temp_row_count + lstLine.Count, 4]);
                    range.Merge(range.MergeCells);
                    range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "<-------- 至");
                    range.Borders.LineStyle = 1;

                    //处理套管颜色
                    line_count = lstLine.Count;
                    //每6根一种颜色
                    if (line_count <= 6)
                    {
                        range = tbl.get_Range(tbl.Cells[temp_row_count + 1, 2 + 4], tbl.Cells[temp_row_count + lstLine.Count, 2 + 4]);
                        range.Merge(range.MergeCells);
                        range.set_Value(XlRangeValueDataType.xlRangeValueDefault, m_listColor[5]);
                        range.Borders.LineStyle = 1;
                    }
                    else
                    {
                        range = null;
                        while (line_count > 6)
                        {
                            range = tbl.get_Range(tbl.Cells[temp_row_count + (nIndex * 6) + 1, 2 + 4], tbl.Cells[temp_row_count + (nIndex * 6) + 1 + 5, 2 + 4]);
                            range.Merge(range.MergeCells);
                            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, m_listColor[nIndex % 6]);
                            range.Borders.LineStyle = 1;
                            nIndex++;
                            line_count -= 6;
                        }
                        if (line_count != 0)
                        {
                            range = tbl.get_Range(tbl.Cells[temp_row_count + (nIndex * 6) + 1, 2 + 4], tbl.Cells[temp_row_count + (nIndex * 6) + line_count, 2 + 4]);
                            range.Merge(range.MergeCells);
                            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, m_listColor[nIndex % 6]);
                            range.Borders.LineStyle = 1;
                        }
                    }

                }

            }

        }


        //画表格头
        private void DrawTableHeader(Worksheet tbl,String name)
        {

            Range range = tbl.get_Range("E1", "O1");
            range.Merge(range.MergeCells);
            range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
            range.Font.Bold = true;
            range.Borders.LineStyle = 1;
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, name + "光配单元配线表");

            range = tbl.get_Range("E2","G2");
            range.Merge(range.MergeCells);
            range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
            range.Borders.LineStyle = 1;
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, name + "光缆");


            range = tbl.get_Range("H2", "H3");
            range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
            range.Merge(range.MergeCells);
            range.Borders.LineStyle = 1;

            range = tbl.get_Range("I2", "K2");
            range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
            range.Merge(range.MergeCells);
            range.Borders.LineStyle = 1;

            range = tbl.get_Range("H2", "H3");
            range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
            range.Merge(range.MergeCells);
            range.Borders.LineStyle = 1;

            range = tbl.get_Range("I2", "K2");
            range.Merge(range.MergeCells);
            range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
            range.Borders.LineStyle = 1;

            range = tbl.get_Range("M2", "O2");
            range.Merge(range.MergeCells);
            range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
            range.Borders.LineStyle = 1;

            range = tbl.get_Range(tbl.Cells[3,1+4],tbl.Cells[3,1+4]);
            range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
            range.Borders.LineStyle = 1;
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "纤芯序号");


            range = tbl.get_Range(tbl.Cells[3, 2 + 4], tbl.Cells[3, 2 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "套管颜色");
            range.Borders.LineStyle = 1;

            range = tbl.get_Range(tbl.Cells[2, 4 + 4], tbl.Cells[2, 4 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "光配单元端子号");
            range.Borders.LineStyle = 1;

            range = tbl.get_Range(tbl.Cells[3, 3 + 4], tbl.Cells[3, 3 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "光纤颜色");
            range.Borders.LineStyle = 1;

            range = tbl.get_Range(tbl.Cells[2, 5 + 4], tbl.Cells[2, 5 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "柜内设备配线");
            range.Borders.LineStyle = 1;

            range = tbl.get_Range(tbl.Cells[3, 5 + 4], tbl.Cells[3, 5 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "尾纤/接口类型");
            range.Borders.LineStyle = 1;

            range = tbl.get_Range(tbl.Cells[3, 6 + 4], tbl.Cells[3, 6 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "装置名称");
            range.Borders.LineStyle = 1;

            range = tbl.get_Range(tbl.Cells[3, 7 + 4], tbl.Cells[3, 7 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "装置插件/端口号");
            range.Borders.LineStyle = 1;


            range = tbl.get_Range(tbl.Cells[2, 8 + 4], tbl.Cells[2, 8 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "备注");
            range.Borders.LineStyle = 1;


            range = tbl.get_Range(tbl.Cells[2, 9 + 4], tbl.Cells[2, 9 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "对侧装置");
            range.Borders.LineStyle = 1;

            range = tbl.get_Range(tbl.Cells[3, 9 + 4], tbl.Cells[3, 9 + 4]);
            range.Borders.LineStyle = 1;
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "装置名称");


            range = tbl.get_Range(tbl.Cells[3, 10 + 4], tbl.Cells[3, 10 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "接口类型");
            range.Borders.LineStyle = 1;

            range = tbl.get_Range(tbl.Cells[3, 11 + 4], tbl.Cells[3, 11 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, "插件端口号");
            range.Borders.LineStyle = 1;

        }


        //画每行的数据
        private void DrawRows(Worksheet tbl, line_info item, int rows, int line_count)
        {
            int index = (line_count -1) % m_listColor.Length;
            Range range = tbl.get_Range(tbl.Cells[rows, 1 + 4], tbl.Cells[rows, 1 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, line_count.ToString());
            range.Borders.LineStyle = 1;

            range = tbl.get_Range(tbl.Cells[rows, 3 + 4], tbl.Cells[rows, 3 + 4]);
            range.set_Value(XlRangeValueDataType.xlRangeValueDefault, m_listColor[index]);
            range.Borders.LineStyle = 1;

            range = tbl.get_Range(tbl.Cells[rows, 4 + 4], tbl.Cells[rows, 4 + 4]);
            range.Borders.LineStyle = 1;
            if (null != item.gp_row)
            {
                int temp = Convert.ToInt32(item.gp_row);
                range.set_Value(XlRangeValueDataType.xlRangeValueDefault, m_listRow[temp] + "-" + item.gp_col.ToString());
            }

            range = tbl.get_Range(tbl.Cells[rows, 5 + 4], tbl.Cells[rows, 5 + 4]);
            range.Borders.LineStyle = 1;
            if (null != item.interface1)
            {
                int temp = Convert.ToInt32(item.gp_row);
                range.set_Value(XlRangeValueDataType.xlRangeValueDefault, item.interface1);
            }

            range = tbl.get_Range(tbl.Cells[rows, 6 + 4], tbl.Cells[rows, 6 + 4]);
            range.Borders.LineStyle = 1;
            if (null != item.iedname1)
            {
                int temp = Convert.ToInt32(item.gp_row);
                range.set_Value(XlRangeValueDataType.xlRangeValueDefault, item.iedname1);
            }


            range = tbl.get_Range(tbl.Cells[rows, 7 + 4], tbl.Cells[rows, 7 + 4]);
            range.Borders.LineStyle = 1;
            if (null != item.sendPortName)
            {
                int temp = Convert.ToInt32(item.gp_row);
                range.set_Value(XlRangeValueDataType.xlRangeValueDefault, item.sendPortName);
            }

            range = tbl.get_Range(tbl.Cells[rows, 8 + 4], tbl.Cells[rows, 8 + 4]);
            range.Borders.LineStyle = 1;
            if (null != item.remark)
            {
                int temp = Convert.ToInt32(item.gp_row);
                range.set_Value(XlRangeValueDataType.xlRangeValueDefault, item.remark);
            }

            range = tbl.get_Range(tbl.Cells[rows, 9 + 4], tbl.Cells[rows, 9 + 4]);
            range.Borders.LineStyle = 1;
            if (null != item.sendPortName)
            {
                int temp = Convert.ToInt32(item.gp_row);
                range.set_Value(XlRangeValueDataType.xlRangeValueDefault, item.sendPortName);
            }


            range = tbl.get_Range(tbl.Cells[rows, 10 + 4], tbl.Cells[rows, 10 + 4]);
            range.Borders.LineStyle = 1;
            if (null != item.iedname2)
            {
                int temp = Convert.ToInt32(item.gp_row);
                range.set_Value(XlRangeValueDataType.xlRangeValueDefault, item.iedname2);
            }


            range = tbl.get_Range(tbl.Cells[rows, 11 + 4], tbl.Cells[rows, 11 + 4]);
            range.Borders.LineStyle = 1;
            if (null != item.interface2)
            {
                int temp = Convert.ToInt32(item.gp_row);
                range.set_Value(XlRangeValueDataType.xlRangeValueDefault, item.interface2);
            }

            /*
            range = tbl.get_Range(tbl.Cells[rows, 11+ 4], tbl.Cells[rows, 11+ 4]);
            range.Borders.LineStyle = 1;
            if (null != item.recvPort)
            {
                int temp = Convert.ToInt32(item.gp_row);
                range.set_Value(XlRangeValueDataType.xlRangeValueDefault, item.recvPort.ToString());
            }
             * */
        }


        public bool init(int scid)
        {
            m_connection = new MyDatabase();

            if (m_connection.init_connection(scid) == false)
            {
                System.Windows.Forms.MessageBox.Show("数据库初始化失败");
                return false;
            }

            return true;
        }

        public void close()
        {
            m_connection.close_connection();
        }

    }
}
